
use rusqlite::{Connection, Result};
use rusqlite::Error::InvalidColumnType;
use rusqlite::types::Type;
use crate::domin::{Goods, GoodVO, Money, ResultVO};
use crate::data::db_path;


/// # 查询 Printer 综合信息
#[allow(dead_code)]
pub fn goods_result_all() -> Result<String> {
    let sql = "select
  tg.id, tg.category_id, tg.type_name, tg.color, tg.name , tg.price, tg.count,  tr.count as sell_count
from tb_goods tg left join tb_repo tr on tg.id = tr.id;";
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map([],|row|{
        Ok(GoodVO{
            id: row.get(0)?,
            category_id: row.get(1)?,
            type_name: row.get(2)?,
            color: row.get(3)?,
            name: row.get(4)?,
            price: row.get(5)?,
            count: row.get(6)?,
            sell_count: row.get(7)?,
        })
    }).expect("查询结果集失败");

    let mut result = Vec::new();
    let _ = &rows.for_each(|row|{
        result.push(row.unwrap());
    });
    Ok(serde_json::to_string(&result).unwrap())
}

/// # 查询综合信息
/// tb_category.name <br>
/// tb_goods.* - tb_goods.count <br>
/// tb_repo.count
#[allow(dead_code)]
pub fn result_all() -> Result<String> {
    let sql = "select tc.id as category_id , tc.name as category_name ,
       tg.id as goods_id , tg.type_name , tg.color , tg.name as goods_name ,
       tg.price , tg.count , tr.count as sell_count, tg.transport , tg.money
from tb_goods tg left join tb_repo tr on tg.id = tr.id left join tb_category tc on tg.category_id = tc.id;";

    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map((), |row| {
        Ok(ResultVO{
            category_id: row.get(0)?,
            category_name: row.get(1)?,
            goods_id: row.get(2)?,
            type_name: row.get(3)?,
            color: row.get(4)?,
            goods_name: row.get(5)?,
            price: row.get(6)?,
            count: row.get(7)?,
            sell_count: row.get(8)?,
            transport: row.get(9)?,
            money: row.get(10)?,
        })
    })?;
    let mut vec = Vec::new();
    for row in rows {
        vec.push(row.unwrap());
    }
    Ok(serde_json::to_string(&vec).expect("获取数据失败"))
  }

#[tauri::command]
/// # 添加商品表
#[allow(dead_code)]
pub fn goods_insert(goods: Goods) -> usize {
    let sql = "insert into tb_goods values (?,?,?,?,?,?,?,?,?);";
    let mut conn = Connection::open(db_path()).expect("打开数据库失败");
    // 开启事务
    let transaction = conn.transaction().expect("开户事务出错");
    let goods_i = transaction.execute(sql, [&goods.id, &goods.category_id, &goods.type_name, &goods.color,
        &goods.name, &goods.price.to_string(), &goods.count.to_string(), &goods.transport.to_string(), &goods.money.to_string()]).expect("添加goods事务失败");
    let repo_i = transaction.execute("insert into tb_repo (id,count)values (?,0);", [&goods.id]).expect("添加repo事务失败");
    // 提交事务
    transaction.commit().expect("添加商品及库存失败");
    let mut i = 0 ;
    if goods_i == 1 && repo_i == 1 {
        i = 1;
    }
    i
}

/// # 删除商品表内容
#[allow(dead_code)]
pub fn goods_delete(id: String) -> Result<usize> {
    let sql = "delete from tb_goods where id = ?";
    let mut conn = Connection::open(db_path()).expect("获取Connection出错!!!");
    let transaction = conn.transaction().expect("事务出错");
    let good_i = transaction.execute(sql, [&id]).expect("事务出错");
    let repo_i = transaction.execute("delete from tb_repo where id = ? ", [&id]).expect("事务出错");
    let _order_i = transaction.execute("delete from tb_order where goods_id = ? ", [&id]).expect("事务出错");
    let _ = transaction.commit() ;
    let mut i = 0 ;

    if good_i == 1 && repo_i == 1 {
        i = 1;
    }
    Ok(i)
}

/// # 修改商品表
#[allow(dead_code)]
pub fn goods_update(goods: Goods) -> Result<usize> {
    let sql = "update tb_goods set category_id = ? , type_name = ? ,color = ? , name = ? ,
                    price = ? , count = ? , transport = ? , money = ? where id = ? ;";
    let conn = Connection::open(db_path()).expect("获取Connection出错!!!");
    let mut stat = conn.prepare(sql).expect("执行sql语句出错");
    let i = stat.execute([goods.category_id, goods.type_name, goods.color, goods.name,
        goods.price.to_string(), goods.count.to_string(), goods.transport.to_string(), goods.money.to_string(), goods.id])?;
    Ok(i)
}

/// # 查找商品表
#[allow(dead_code)]
pub fn goods_search(goods: Goods) -> Result<String> {
    let mut sql = "select * from tb_goods ".to_string();
    if goods.category_id != "" {
        sql = format!("{}{}", sql, "where category_id = ? ");
        let conn = Connection::open(db_path())?;
        let mut stat = conn.prepare(&*sql)?;
        let rows = stat.query_map([goods.category_id], |row| {
            Ok(Goods {
                id: row.get(0)?,
                category_id: row.get(1)?,
                type_name: row.get(2)?,
                color: row.get(3)?,
                name: row.get(4)?,
                price: row.get(5)?,
                count: row.get(6)?,
                transport: row.get(7)?,
                money: row.get(8)?,
            })
        })?;
        let mut vec = Vec::new();
        for g in rows {
            vec.push(g.unwrap())
        }
        let s = serde_json::to_string(&vec).expect("序列化数据出错");

        Ok(s)
    } else {
        goods_search_all()
    }


}
#[allow(dead_code)]
pub fn goods_search_all() -> Result<String> {
    let sql = "select * from tb_goods ";
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map([], |row| {
        Ok(Goods {
            id: row.get(0)?,
            category_id: row.get(1)?,
            type_name: row.get(2)?,
            color: row.get(3)?,
            name: row.get(4)?,
            price: row.get(5)?,
            count: row.get(6)?,
            transport: row.get(7)?,
            money: row.get(8)?,
        })
    })?;
    let mut vec = Vec::new();
    for g in rows {
        vec.push(g.unwrap())
    }
    let s = serde_json::to_string(&vec).expect("序列化数据出错");
    Ok(s)
}
#[allow(dead_code)]
pub fn goods_find_by_page(page: i32, size: i32) -> Result<String> {
    let current_page = (page - 1) * size ;
    let sql = format!("select id,category_id,type_name,color,name,price,count,transport,money from tb_goods limit {} , {} ", current_page , size);
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(&sql)?;
    let rows = stat.query_map([], |row| {
        Ok(Goods {
            id: row.get(0)?,
            category_id: row.get(1)?,
            type_name: row.get(2)?,
            color: row.get(3)?,
            name: row.get(4)?,
            price: row.get(5)?,
            count: row.get(6)?,
            transport: row.get(7)?,
            money: row.get(8)?,
        })
    })?;
    let mut vec = Vec::new() ;
    rows.for_each(|row|{
        vec.push(row.unwrap());
    });
    Ok(serde_json::to_string(&vec).expect("获取goods表数据失败"))
}

/// # 查询总价格
pub fn get_goods_money () -> Result<String> {
    let sql = "select sum(money) as value, count(id) as count from tb_goods ;";
    let conn = Connection::open(db_path())?;
    let mut stat = conn.prepare(sql)?;
    let rows = stat.query_map([],|row|{
        Ok(Money{
            value : row.get(0)?,
            count : row.get(1)?
        })
    })?;
    let mut vec = Vec::new();
    let mut s = String::new() ;
    rows.for_each(|row|{
        if row == Err(InvalidColumnType(0, "value".to_string(), Type::Null)){
            s = "".to_string();
        }else{
            let money = row.expect("获取Money数据失败") ;
            vec.push(money);
            s = serde_json::to_string(&vec).expect("序列化数据出错");
        }
    });
    Ok(s)
}


